//*********************************************************
//
//    Copyright (c) Microsoft. All rights reserved.
//    This code is licensed under the Apache License, Version 2.0.
//    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
//    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
//    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
//    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
//
//*********************************************************

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Globalization;
using System.Data;
using System.Text.RegularExpressions;
using System.Configuration.Install;

namespace TridentDbInstaller
{
    enum TridbAction
    {
        CreateNew = 0,
        DropAll = 1,
        ReCreate = 2,
        CleanupDb = 3,
        VerifyServer = 4
    }
    enum TriDbError
    {
        ErrorSuccess = 0,
        ServerConnFailed,
        DatabaseError,
        ScriptExecError,
        DatabaseExists
    }
    enum TriDbAuthentication
    {
        None = 0,
        TrustedAuthentication = 1,
        SQLServerAuthentication = 2,
    }

    class InstallDB
    {
        string[] cleanupDbFilters = new string[] { "drop_views.sql", "drop_tables.sql", "drop_storeprocs.sql" };

        string[] createDbFilters = new string[] { "create_table_p1_*", "create_table_p2_*", "create_relation_*",
                                        "create_rel_sp_add_*","create_rel_sp_update_*", "create_rel_sp_delete_*",
                                        "create_rel_sp_count_*","create_view_count_*", "create_view_base_*", 
                                        "create_sp_add_*","create_sp_delete_*","create_sp_update_*","create_sp_count_*",
                                        "create_sp_search_*","create_sp_get_*","create_sp_getbyid_*","create_sp_static_*"};

        string[] dropAllCommands = new string[] { "ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE",
	                                              "ALTER DATABASE [{0}] SET SINGLE_USER",
	                                              "DROP DATABASE [{0}]"};

        public InstallDB(InstallContext ctx, string userName, string password, int authentication)
        {
            this.context = ctx;
            this.userName = userName;
            this.password = password;

            if (authentication == (int)TriDbAuthentication.TrustedAuthentication)
            {
                this.trustedAuthentication = TriDbAuthentication.TrustedAuthentication;
            }
            else if (authentication == (int)TriDbAuthentication.SQLServerAuthentication)
            {
                this.trustedAuthentication = TriDbAuthentication.SQLServerAuthentication;
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbServer"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private SqlConnection OpenSqlConnection(string dbServer, string dbName)
        {
            SqlConnection sqlConn = null;
            //try 3 times to connect to Db
            for (int i = 0; (i < retryCount && null == sqlConn); i++)
            {
                try
                {
                    string connectionString = string.Empty;
                    if (this.trustedAuthentication == TriDbAuthentication.TrustedAuthentication)
                    {
                        connectionString = string.Format(CultureInfo.InvariantCulture, @"Server={0};Integrated Security=True;Database={1}", dbServer, dbName);
                    }
                    else if (this.trustedAuthentication == TriDbAuthentication.SQLServerAuthentication)
                    {
                        connectionString = string.Format(CultureInfo.InvariantCulture, @"Server={0};Integrated Security=False;Database={1};User Id={2};Password={3};", dbServer, dbName, this.userName, this.password);
                    }

                    sqlConn = new SqlConnection(connectionString);
                    sqlConn.Open();
                }
                catch (SqlException ex)
                {
                    this.LogMessage("Connection to db server failed retrying", null, ex);
                    sqlConn = null;

                    // If failed wait for sometime before trying again.
                    System.Threading.Thread.Sleep(sleepTime);
                }
            }
            return sqlConn;
        }

        /// <summary>
        /// Check whether database exists or not. 
        /// </summary>
        /// <param name="dbName">Database name.</param>
        /// <param name="error">Error.</param>
        /// <returns>Status of fucntion.</returns>
        private TriDbError IsDbAvailable(string dbServer, string dbName)
        {
            string script = string.Format(CultureInfo.InvariantCulture, "SELECT count(*) FROM sys.databases WHERE [name]='{0}'", dbName);
            TriDbError errorStatus = TriDbError.ServerConnFailed;
            try
            {
                using (SqlConnection conn = OpenSqlConnection(dbServer, "master"))
                {
                    //Failed to connect to DB
                    if (null == conn)
                    {
                        return errorStatus;
                    }

                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.CommandText = script;
                    int count = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);
                    //Db not exists?
                    if (count <= 0)
                    {
                        errorStatus = TriDbError.ErrorSuccess;
                    }
                    else
                    {
                        errorStatus = TriDbError.DatabaseExists;
                    }
                }
            }
            catch (SqlException ex)
            {
                this.LogMessage(null, script, ex);
                //Could not connect to database
                //Log the error 
                errorStatus = TriDbError.ScriptExecError;
            }
            return errorStatus;
        }


        /// <summary>
        /// Manages the SQL data base.
        /// </summary>
        /// <param name="action">The action.</param>
        /// <param name="scriptFolder">The script folder.</param>
        /// <param name="dbServer">The db server.</param>
        /// <param name="dbName">Name of the db.</param>
        /// <param name="useName">Name of the use.</param>
        /// <param name="password">The password.</param>
        /// <param name="authentication">The authentication.</param>
        /// <returns></returns>
        public TriDbError ManageSqlDataBase(TridbAction action, string scriptFolder, string dbServer, string dbName)
        {
            TriDbError status = TriDbError.ServerConnFailed;
            switch (action)
            {
                case TridbAction.CleanupDb:
                    {
                        status = CleanupDb(scriptFolder, dbServer, dbName);
                    }
                    break;
                case TridbAction.CreateNew:
                    {
                        status = IsDbAvailable(dbServer, dbName);
                        if (TriDbError.ErrorSuccess == status)
                        {
                            status = CreateNewDb(scriptFolder, dbServer, dbName);
                        }
                    }
                    break;

                case TridbAction.DropAll:
                    {
                        status = DropDb(dbServer, dbName);
                    }
                    break;
                case TridbAction.ReCreate:
                    {
                        status = DropDb(dbServer, dbName);
                        if (TriDbError.ErrorSuccess == status)
                        {
                            status = CreateNewDb(scriptFolder, dbServer, dbName);
                        }
                    }
                    break;
                case TridbAction.VerifyServer:
                    {
                        status = IsDbAvailable(dbServer, dbName);
                    }
                    break;
            }
            return status;
        }
        private TriDbError DropDb(string dbServer, string dbName)
        {
            TriDbError status = TriDbError.ServerConnFailed;
            //Create new Db
            using (SqlConnection conn = OpenSqlConnection(dbServer, "master"))
            {
                if (null != conn)
                {
                    cachedConnection = conn;
                    foreach (string sqlCmd in dropAllCommands)
                    {
                        string command = string.Format(sqlCmd, dbName);
                        status = ExecuteScript(command);
                        if (TriDbError.ErrorSuccess != status)
                        {
                            break;
                        }
                    }
                }
            }
            return status;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbServer"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private TriDbError CreateNewDb(string scriptFolder, string dbServer, string dbName)
        {
            TriDbError status = TriDbError.ServerConnFailed;
            //Create new Db
            using (SqlConnection conn = OpenSqlConnection(dbServer, "master"))
            {
                if (null != conn)
                {
                    cachedConnection = conn;
                    string sqlScript = String.Format("CREATE DATABASE [{0}]", dbName);
                    status = ExecuteScript(sqlScript);
                    if (TriDbError.ErrorSuccess != status)
                    {
                        return status;
                    }
                }
            }
            //create tables and views
            using (SqlConnection conn = OpenSqlConnection(dbServer, dbName))
            {
                cachedConnection = conn;
                transaction = conn.BeginTransaction();
                foreach (string filter in createDbFilters)
                {
                    status = ExecuteScriptFile(scriptFolder, filter);
                    if (TriDbError.ErrorSuccess != status)
                    {
                        transaction.Rollback();
                        transaction = null;
                        return status;
                    }
                }
                transaction.Commit();
                transaction = null;
            }
            return status;
        }
        /// <summary>
        /// 
        /// </summary>
        private TriDbError CleanupDb(string scriptFolder, string dbServer, string dbName)
        {
            TriDbError status = TriDbError.ServerConnFailed;
            //delete all tables, views and Sp 
            using (SqlConnection conn = OpenSqlConnection(dbServer, dbName))
            {
                if (null != conn)
                {
                    cachedConnection = conn;
                    transaction = conn.BeginTransaction();
                    foreach (string filter in cleanupDbFilters)
                    {
                        status = ExecuteScriptFile(scriptFolder, filter);
                        if (TriDbError.ErrorSuccess != status)
                        {
                            transaction.Rollback();
                            transaction = null;
                            return status;
                        }
                    }

                    foreach (string filter in createDbFilters)
                    {
                        status = ExecuteScriptFile(scriptFolder, filter);
                        if (TriDbError.ErrorSuccess != status)
                        {
                            transaction.Rollback();
                            transaction = null;
                            return status;
                        }
                    }
                    transaction.Commit();
                    transaction = null;
                }
            }
            return status;
        }
        /// <param name="scriptFile"></param>
        /// <returns></returns>
        private TriDbError ExecuteScriptFile(string scriptFolder, string filter)
        {
            TriDbError status = TriDbError.ScriptExecError;

            DirectoryInfo scriptDir = new DirectoryInfo(scriptFolder);
            FileInfo[] cleanFiles = scriptDir.GetFiles(filter);
            foreach (FileInfo fi in cleanFiles)
            {
                using (TextReader reader = new StreamReader(fi.FullName, System.Text.Encoding.ASCII))
                {
                    string sqlQuery = reader.ReadToEnd();
                    this.LogMessage(fi.FullName, sqlQuery, null);
                    status = ExecuteScript(sqlQuery.ToString());
                    if (TriDbError.ErrorSuccess != status)
                    {
                        reader.Close();
                        break;
                    }
                    reader.Close();
                }
            }
            return status;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlScript"></param>
        /// <returns></returns>
        private TriDbError ExecuteScript(string sqlScript)
        {
            //handle null scripts 
            if (String.IsNullOrEmpty(sqlScript))
                return TriDbError.ErrorSuccess;

            TriDbError status = TriDbError.ScriptExecError;
            //retry executing the same query multiple times
            StringBuilder script = new StringBuilder();
            for (int i = 0; i < retryCount && status != TriDbError.ErrorSuccess; i++)
            {
                try
                {
                    Regex regex = new Regex("^\\s*GO\\s*$", RegexOptions.IgnoreCase | RegexOptions.Multiline);
                    string[] lines = regex.Split(sqlScript);
                    using (SqlCommand cmd = new SqlCommand(lines[0], cachedConnection))
                    {
                        script.AppendLine(cmd.CommandText);

                        if (null != transaction)
                        {
                            cmd.Transaction = transaction;
                        }
                        cmd.ExecuteScalar();
                        status = TriDbError.ErrorSuccess;
                    }
                }
                catch (SqlException ex)
                {
                    this.LogMessage(null, script.ToString(), ex);
                }
            }
            return status;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        /// <param name="ex"></param>
        void LogMessage(string message, string script, Exception ex)
        {
            if (null != message)
            {
                context.LogMessage(message);
            }

            if (null != script)
            {
                string[] scriptLines = script.Replace("\r", "").Split('\n');
                for (int i = 0; i < scriptLines.Length; i++)
                {
                    context.LogMessage(string.Format("\t{0,3}: {1}", i, scriptLines[i]));
                }
            }

            if (null != ex)
            {
                context.LogMessage(ex.Message + ex.StackTrace);
            }

        }
        SqlTransaction transaction = null;
        SqlConnection cachedConnection = null;
        const int sleepTime = 50; //100 milliseconds timeout between next connection to db
        const int retryCount = 3;
        InstallContext context = null;
        string userName = string.Empty;
        string password = string.Empty;
        TriDbAuthentication trustedAuthentication = TriDbAuthentication.None;
    }
}
